﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DreamCMS.WebCore.Data
{
    internal class SqlTextTools
    {
        #region 获取分页SQL
        /// <summary>
        /// 获取分页Sql
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">排序字段</param>
        /// <param name="colList">要查询的结果字段</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="orderBy">排序类型（升序or降序）0是升序，1是降序</param>
        /// <param name="condition">查询过滤条件</param>
        /// <returns>格式化后的分页SQL语句</returns>
        public static string GetPageSql(string tableName, string colName, string colList, long pageSize, long pageIndex, long orderBy, string condition)
        {
            string temp = string.Empty;
            string sql = string.Empty;
            if (string.IsNullOrEmpty(condition))
            {
                condition = " 1=1 ";
            }

            //降序
            if (orderBy == 1)
            {
                //TODO:后续版本升级
                //以下SQL语句貌似写的很烂，以后改进！
                temp = "select top {0} {1} from {2} where {5} and {3} <(select min(pk) from ( select top {4} {3} as pk from {2} where {5} order by {3} desc) t) order by {3} desc";
                sql = string.Format(temp, pageSize, colList, tableName, colName, pageSize * (pageIndex - 1), condition);
            }
            //升序
            if (orderBy == 0)
            {
                temp = "select top {0} {1} from {2} where {5} and {3} >(select max(pk) from ( select top {4} {3} as pk from {2} where {5} order by {3} asc) t) order by {3} asc";
                sql = string.Format(temp, pageSize, colList, tableName, colName, pageSize * (pageIndex - 1), condition);
            }
            //第一页
            if (pageIndex == 1)
            {
                temp = "select top {0} {1} from {2} where {3} order by {4} {5}";
                sql = string.Format(temp, pageSize, colList, tableName, condition, colName, orderBy == 1 ? "desc" : "asc");
            }

            return sql;
        }
        #endregion
    }
}
